package common.mq.persistence.service.impl;

import java.lang.reflect.Type;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.google.gson.Gson;

import common.mq.persistence.po.DBResult;
import common.mq.persistence.service.IPersistenceService;

public class PersistenceServiceImpl<T,V> implements IPersistenceService<T,V> {

	/**
	 * JdbcTemplate对象
	 */
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	
	@Override
	public void excuteSql(List<String> sqls){
		for(String sql : sqls){
			jdbcTemplate.update(sql);
		}
	}

	/**
	 * 序列化并持久化对象
	 * @param entry
	 */
	private void saveEntry(Map.Entry<String, Map<T,V>> entry,boolean isAppend){
		String tableName = entry.getKey().toUpperCase();
		Map<T,V> datas = entry.getValue();
		Set<Map.Entry<T,V>> set = datas.entrySet();
		if(!isTableExists(tableName)){
			jdbcTemplate.update("CREATE TABLE " +  tableName + " (sKey VARCHAR(50), sValue CLOB)");
		}else{
			if(!isAppend){
				jdbcTemplate.update("DELETE FROM  " + tableName);
			}
		}
		for (Map.Entry<T,V> item : set) {
			String id = new Gson().toJson(item.getKey());
			String value = new Gson().toJson(item.getValue()); // 序列化值
			String sql = "INSERT INTO " +  tableName + " VALUES('" + id +"','" + value +"')";
			jdbcTemplate.update(sql);
		}
		
	}
	
	/**
	 * 新建表格
	 * 如果表格已存在则先删除再新增
	 * @param tableName
	 */
	public void createTable(String tableName){
		if(isTableExists(tableName)){
			jdbcTemplate.update("DROP TABLE  " + tableName);
		}
		jdbcTemplate.update("CREATE TABLE " +  tableName + " (sKey VARCHAR(50), sValue CLOB)");
	}
	
	/**
	 * 批量新建表格
	 * 如果表格已存在则先删除再新增
	 * @param tableNames
	 */
	public void createTable(List<String> tableNames){
		for(String tableName: tableNames){
			createTable(tableName);
		}
	}
	
	private boolean isTableExists(String tableName){
		DatabaseMetaData meta;
		HashSet<String> set=new HashSet<String>();  
		try {
			meta = jdbcTemplate.getDataSource().getConnection().getMetaData();
			ResultSet res = meta.getTables(null, null, null, new String[]{"TABLE"});  
	        while (res.next()) {  
	            set.add(res.getString("TABLE_NAME"));  
	        }  
	        System.out.println(set);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		
        if(set.contains(tableName.toUpperCase())){
        	return true;
        }
        return false;
	}
	
	/**
	 * 保存对象
	 * isAppend --true 追加 、false 清空表格后保存
	 */
	@Override
	public boolean save(Map<String, Map<T,V>> map,boolean isAppend) {
		Set<Map.Entry<String, Map<T,V>>> set = map.entrySet();
		for (Map.Entry<String, Map<T,V>> entry : set) {
			System.out.println(entry.getKey() + "--->" + entry.getValue());
			saveEntry(entry,isAppend);
		}
		return false;
	}
	
	/**
	 * 追加保存对象
	 */
	@Override
	public boolean save(Map<String, Map<T,V>> map) {
		return save(map, true);
	}

	@Override
	public Map<T,V> recover(String key, Class<T> keyClass, Class<V> valueClass) {
		Map<T,V> map = new HashMap<T,V>();
		String tableName = key.toUpperCase();
		String sql = "SELECT * FROM " + tableName ;
		List<DBResult> resultList = jdbcTemplate.query(sql, new DBResultRowMapper());
		for(DBResult r : resultList){
			map.put(new Gson().fromJson(r.getsKey(), keyClass), new Gson().fromJson(r.getsValue(), valueClass));
		}
		return map;
	}
	
	/**
	 * 值为集合类型的还原
	 */
	@Override
	public Map<T, Object> recover(String key, Class<T> keyClass, Type valueType) {
		Map<T, Object> map = new HashMap<T, Object>();
		List<DBResult> resultList = getDBResultList(key);
		for(DBResult r : resultList){
			map.put(new Gson().fromJson(r.getsKey(), keyClass), new Gson().fromJson(r.getsValue(), valueType));
		}
		return map;
	}
	
	public List<DBResult> getDBResultList(String key){
		String tableName = key.toUpperCase();
		String sql = "SELECT * FROM " + tableName ;
		return jdbcTemplate.query(sql, new DBResultRowMapper());
	}
	
	public class DBResultRowMapper implements RowMapper<DBResult> {
		public DBResult mapRow(ResultSet rs, int index) throws SQLException {
			DBResult dr = new DBResult();
			dr.setsKey(rs.getString("sKey"));
			dr.setsValue(rs.getString("sValue"));
			return dr;
		}
	}

}
